﻿// HSS.Data.DataAccessService.cs
// ----------------------------------------------------------------------------
// Licensed under the MIT license
// http://www.opensource.org/licenses/mit-license.html
// ----------------------------------------------------------------------------
// HighSpeed-Solutions, LLC
// Copyright (c) 2001-2010
// ----------------------------------------------------------------------------
// File:       DataAccessService.cs
// Author:     HSS\gbanta
// Created:    08/12/2010
// Modified:   03/07/2011
// ----------------------------------------------------------------------------
namespace HSS.Data
{
	#region Using Directives
	using System;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.ComponentModel;
	using System.Data;
	using System.Data.SqlClient;
	using HSS.Logging;
	using System.Linq;
	#endregion

	#region DataAccessService<TDataEntity>
	/// <summary>
	/// Provides generic data access services.
	/// </summary>
	/// <typeparam name="TDataEntity">An IDataEntity Object</typeparam>
	public static class DataAccessService<TDataEntity> where TDataEntity : IDataEntity, new()
	{
		#region Delegates
		/// <summary>
		/// CanLoadItem is a delegate that is called prior to adding a <see cref="IDataEntity"/>
		/// object to a <see cref="List{T}"/>, <see cref="HashSet{T}"/> or <see cref="Collection{T}"/>,
		/// allowing the caller to optional cancel or skip the item.
		/// </summary>
		/// <param name="item">The <see cref="IDataEntity"/> object about to be added</param>
		/// <param name="e">CancelEventArgs allowing the caller to indicate the 
		/// item should be skipped
		/// </param>
		public delegate void CanLoadItem(TDataEntity item, System.ComponentModel.CancelEventArgs e);
		#endregion

		#region Methods

		#region Helpers
		private static DataAccessConnection ResolveConnection()
		{
			return DataAccessContext.GetRegisteredOrDefault<TDataEntity>();
		}
		private static string ResolveConnectionString()
		{
			var dac = ResolveConnection();
			if (null != dac)
				return dac.ConnectionString;
			throw new DataAccessConnectionNullException(Strings.DataAccessService_UnableToResolveConnection);
		}
		private static bool AssignParameters(DataAccess da, DataParameter[] parameters)
		{
			bool hasOutputParameters = false;
			if (null != parameters)
			{
				foreach (var p in parameters)
				{
					switch (p.Direction)
					{
						case ParameterDirection.Input:
							da.AddWithValue(p.Name, p.Value);
							break;

						case ParameterDirection.InputOutput:
						case ParameterDirection.Output:
							da.AddWithValue(p.Name, p.Value, p.Direction);
							hasOutputParameters = true;
							break;

						case ParameterDirection.ReturnValue:
							throw new NotSupportedException("This method does not support the ReturnValue parameter.");
					}
				}
			}
			return hasOutputParameters;
		}
		private static void ProcessParameters(DataAccess da, DataParameter[] parameters)
		{
			foreach (var prm in da.SqlOutParameters)
			{
				var prmName = prm.ParameterName.Replace("@", "");
				foreach (var p in parameters)
				{
					var pName = p.Name.Replace("@", "");
					if (pName == prmName)
					{
						p.Value = prm.Value;
						break;
					}
				}
			}
		}
		#endregion

		#region Get

		/// <summary>
		/// Gets a specific <typeparamref name="TDataEntity"/> item from the database
		/// </summary>
		/// <typeparam name="O">The IDataEntityGet object to retreive</typeparam>
		/// <param name="id">The id of the object to get</param>
		/// <returns>The requested object if found; otherwise null</returns>
		public static TDataEntity GetItem<O>(object id)
			where O : IDataEntityGet, TDataEntity, new()
		{
			O item = new O();
			using (var da = new DataAccess(ResolveConnection()))
				return (TDataEntity)item.Get(da, id);
		}
		/// <summary>
		/// Gets a single record (<typeparamref name="TDataEntity"/>) of data from the database
		/// assuming the stored procedure takes one parameter named 'ApplicationName'.
		/// </summary>
		/// <param name="sprocName">The name of the stored procedure</param>
		/// <param name="applicationName">The application name.</param>
		/// <returns>An object of type TDataEntity</returns>
		public static TDataEntity GetData(string sprocName, string applicationName)
		{
			TDataEntity data = default(TDataEntity);
			using (var da = new DataAccess(ResolveConnection()))
			{
				da.AddWithValue("ApplicationName", applicationName);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					if (dr.Read())
					{
						data = new TDataEntity();
						data.Load(dr);
					}
				}
			}
			return data;
		}
		/// <summary>
		/// Gets a single record (<typeparamref name="TDataEntity"/>) of data from the database.
		/// </summary>
		/// <param name="sprocName">The name of the stored procedure</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>An object of type TDataEntity</returns>
		public static TDataEntity GetData(string sprocName, params DataParameter[] parameters)
		{
			TDataEntity data = default(TDataEntity);
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					if (dr.Read())
					{
						data = new TDataEntity();
						data.Load(dr);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return data;
		}

		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="List{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <returns>A <see cref="List{T}"/> of type TDataEntity objects</returns>
		public static List<TDataEntity> GetList(string sprocName)
		{
			var list = new List<TDataEntity>();

			using (var da = new DataAccess(ResolveConnection()))
			{
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						list.Add(data);
					}
				}
			}

			return list;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="List{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="List{T}"/> of type TDataEntity objects</returns>
		public static List<TDataEntity> GetList(string sprocName, params DataParameter[] parameters)
		{
			var list = new List<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						list.Add(data);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return list;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="List{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="loadCallback">Prior to adding a new <see cref="IDataEntity"/> Object to the <see cref="List{T}"/>, this delegate is called, allowing the caller to skip the Object</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="List{T}"/> of type TDataEntity objects</returns>
		public static List<TDataEntity> GetList(string sprocName, CanLoadItem loadCallback, params DataParameter[] parameters)
		{
			Guard.ArgumentNotNull(loadCallback, "loadCallback");
			var list = new List<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						var e = new CancelEventArgs();
						loadCallback(data, e);
						if (!e.Cancel)
							list.Add(data);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return list;
		}


		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">Failed to add a DataEntity to the HashSet.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName)
		{
			var set = new HashSet<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						if (!set.Add(data))
							throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_FailedToAdd);
					}
				}
			}
			return set;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">Failed to add a DataEntity to the HashSet.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName, params DataParameter[] parameters)
		{
			var set = new HashSet<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						if (!set.Add(data))
							throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_FailedToAdd);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return set;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="loadCallback">Prior to adding a new <see cref="IDataEntity"/> Object to the <see cref="HashSet{T}"/>, this delegate is called, allowing the caller to skip the Object</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">HashSet does not support duplicate items.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName, CanLoadItem loadCallback, params DataParameter[] parameters)
		{
			Guard.ArgumentNotNull(loadCallback, "loadCallback");
			var set = new HashSet<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						var e = new CancelEventArgs();
						loadCallback(data, e);
						if (!e.Cancel && !set.Add(data))
							throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_NoDuplicatesAllowed);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return set;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type 
		/// requested and uses the specified equality comparer for the set type.
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="dataEntityComparer">The <see cref="IEqualityComparer{T}"/> 
		/// implementation to use when comparing values in the set, or null to use the default 
		/// <see cref="EqualityComparer{T}"/> implementation for the set type.</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">Failed to add a DataEntity to the HashSet.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName, IEqualityComparer<TDataEntity> dataEntityComparer)
		{
			var set = new HashSet<TDataEntity>(dataEntityComparer);
			using (var da = new DataAccess(ResolveConnection()))
			{
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						if (!set.Add(data))
							throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_FailedToAdd);
					}
				}
			}
			return set;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type 
		/// requested and uses the specified equality comparer for the set type.
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="dataEntityComparer">The <see cref="IEqualityComparer{T}"/> 
		/// implementation to use when comparing values in the set, or null to use the default 
		/// <see cref="EqualityComparer{T}"/> implementation for the set type.</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">Failed to add a DataEntity to the HashSet.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName, IEqualityComparer<TDataEntity> dataEntityComparer, params DataParameter[] parameters)
		{
			var set = new HashSet<TDataEntity>(dataEntityComparer);
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						if (!set.Add(data))
							throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_FailedToAdd);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return set;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="HashSet{T}"/> of the type
		/// requested and uses the specified equality comparer for the set type.
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="dataEntityComparer">The <see cref="IEqualityComparer{T}"/> 
		/// implementation to use when comparing values in the set, or null to use the default 
		/// <see cref="EqualityComparer{T}"/> implementation for the set type.</param>
		/// <param name="loadCallback">Prior to adding a new <see cref="IDataEntity"/> Object to the <see cref="HashSet{T}"/>, this delegate is called, allowing the caller to skip the Object</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="HashSet{T}"/> of type TDataEntity objects</returns>
		/// <exception cref="DuplicateDataEntityException">Failed to add a DataEntity to the HashSet.</exception>
		public static HashSet<TDataEntity> GetHashSet(string sprocName, IEqualityComparer<TDataEntity> dataEntityComparer, CanLoadItem loadCallback, params DataParameter[] parameters)
		{
			Guard.ArgumentNotNull(loadCallback, "loadCallback");
			var set = new HashSet<TDataEntity>(dataEntityComparer);
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						var e = new CancelEventArgs();
						loadCallback(data, e);
						if (!e.Cancel)
						{
							if (!set.Add(data))
								throw new DuplicateDataEntityException(Strings.DataAccessService_HashSet_FailedToAdd);
						}
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return set;
		}


		/// <summary>
		/// Executes the requested stored procedure, and populates a <see cref="Collection{T}"/> of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <returns>A <see cref="Collection{T}"/> of type TDataEntity objects</returns>
		public static Collection<TDataEntity> GetCollection(string sprocName)
		{
			var collection = new Collection<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						collection.Add(data);
					}
				}
			}
			return collection;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a Collection of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="Collection{T}"/> of type TDataEntity objects</returns>
		public static Collection<TDataEntity> GetCollection(string sprocName, params DataParameter[] parameters)
		{
			var collection = new Collection<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						collection.Add(data);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return collection;
		}
		/// <summary>
		/// Executes the requested stored procedure, and populates a Collection of the type requested
		/// </summary>
		/// <param name="sprocName">The name of the Stored Procedure to execute</param>
		/// <param name="loadCallback">Prior to adding a new <see cref="IDataEntity"/> Object to the <see cref="Collection{T}"/>, this delegate is called, allowing the caller to skip the Object</param>
		/// <param name="parameters">An array of <see cref="DataParameter"/> objects</param>
		/// <returns>A <see cref="Collection{T}"/> of type TDataEntity objects</returns>
		public static Collection<TDataEntity> GetCollection(string sprocName, CanLoadItem loadCallback, params DataParameter[] parameters)
		{
			Guard.ArgumentNotNull(loadCallback, "loadCallback");
			var collection = new Collection<TDataEntity>();
			using (var da = new DataAccess(ResolveConnection()))
			{
				bool hasOutputParameters = false;
				if (null != parameters)
					hasOutputParameters = AssignParameters(da, parameters);
				using (var dr = da.ExecSqlDataReader(sprocName))
				{
					while (dr.Read())
					{
						TDataEntity data = new TDataEntity();
						data.Load(dr);
						var e = new CancelEventArgs();
						loadCallback(data, e);
						if (!e.Cancel)
							collection.Add(data);
					}
				}
				if (hasOutputParameters)
					ProcessParameters(da, parameters);
			}
			return collection;
		}

		#endregion

		#region Add

		/// <summary>
		/// Adds a new <typeparamref name="TADD"/> to the database.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityAdd{T}"/> object.</typeparam>
		/// <typeparam name="TID">The Type of the Id that will be returned.</typeparam>
		/// <param name="item">An instance of type <typeparamref name="TADD"/> containing the object to insert.</param>
		/// <param name="result">An int containing the return value of the database call.</param>
		/// <returns>The id of the newly inserted Item.</returns>
		public static TID AddItem<TADD, TID>(TADD item, ref int result)
			where TADD : IDataEntityAdd<TID>, TDataEntity
		{
			return AddItem<TADD, TID>(item, null, ref result);
		}
		/// <summary>
		/// Adds a new <typeparamref name="TADD"/> to the database.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityAdd{T}"/> object.</typeparam>
		/// <typeparam name="TID">The Type of the Id that will be returned.</typeparam>
		/// <param name="item">An instance of type <typeparamref name="TADD"/> containing the object to insert.</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <param name="result">An int containing the return value of the database call.</param>
		/// <returns>The id of the newly inserted Item.</returns>
		public static TID AddItem<TADD, TID>(TADD item, object[] state, ref int result)
			where TADD : IDataEntityAdd<TID>, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			TID newId = default(TID);
			using (var da = new DataAccess(ResolveConnection()))
				newId = item.AddNew(da, state, ref result);
			return newId;
		}
		/// <summary>
		/// Adds a new <typeparamref name="TADD"/> to the database using a 
		/// SqlTransaction with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityAdd{T}"/> object</typeparam>
		/// <typeparam name="TID">The Type of the Id that will be returned.</typeparam>
		/// <param name="item">An instance of type <typeparamref name="TADD"/> containing the object to insert</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <param name="result">An int containing the return value of the database call.</param>
		/// <returns>The id of the newly inserted Item.</returns>
		public static TID AddItemTrx<TADD, TID>(TADD item, object[] state, ref int result)
			where TADD : IDataEntityAddTrx<TID>, TDataEntity
		{
			return AddItemTrx<TADD, TID>(item, state, IsolationLevel.ReadCommitted, ref result);
		}
		/// <summary>
		/// Adds a new <typeparamref name="TADD"/> to the database using a SqlTransaction
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityAdd{T}"/> object</typeparam>
		/// <typeparam name="TID">The Type of the Id that will be returned.</typeparam>
		/// <param name="item">An instance of type <typeparamref name="TADD"/> containing the object to insert</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <param name="result">An int containing the return value of the database call.</param>
		/// <returns>The id of the newly inserted Item.</returns>
		public static TID AddItemTrx<TADD, TID>(TADD item, object[] state, IsolationLevel isolationLevel, ref int result)
			where TADD : IDataEntityAddTrx<TID>, TDataEntity
		{
			TID newId = default(TID);
			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{

					newId = AddItemTrx<TADD, TID>(item, command, state, ref result);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (result == 0)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion

				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}
			return newId;
		}
		/// <summary>
		/// Adds a new <typeparamref name="TADD"/> to the database using the
		/// provided SqlCommand, returning the id of the newly inserted Item.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityAdd{T}"/> object</typeparam>
		/// <typeparam name="TID">The Type of the Id that will be returned.</typeparam>
		/// <param name="item">An instance of type <typeparamref name="TADD"/> containing the object to insert</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <param name="result">An int containing the return value of the database call.</param>
		/// <remarks>
		/// Expects the SqlCommand to already be included in a SqlTransaction. Does NOT call Commit or Abort.
		/// </remarks>
		/// <returns>The id of the newly inserted Item.</returns>
		/// <exception cref="Exception">Missing a required SqlTransaction.</exception>
		public static TID AddItemTrx<TADD, TID>(TADD item, SqlCommand command, object[] state, ref int result)
			where TADD : IDataEntityAddTrx<TID>, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			Guard.ArgumentNotNull(command, "command");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			command.Parameters.Clear();
			return item.AddNew(command, state, ref result);
		}

		/// <summary>
		/// Adds a collection of new <typeparamref name="TADD"/> items to the database.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityBatchAdd"/> object.</typeparam>
		/// <param name="items">The collection of items containing the new information.</param>
		/// <returns>true if all items where successfully added; otherwise false.</returns>
		public static bool AddBatch<TADD>(IEnumerable<TADD> items)
			where TADD : IDataEntityBatchAdd, TDataEntity
		{
			Guard.ArgumentNotNull(items, "items");
			if (items.Count() == 0)
				return true;
			bool succcess = false;
			using (var da = new DataAccess(ResolveConnection()))
			{
				foreach (var item in items)
				{
					succcess = item.AddNewBatchItem(da);
					if (!succcess)
						break;
				}
			}
			return succcess;
		}
		/// <summary>
		/// Adds a collection of new <typeparamref name="TADD"/> items to the database using a 
		/// SqlTransaction with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityBatchAddTrx"/> object</typeparam>
		/// <param name="items">The collection items containing the new information.</param>
		/// <returns>true if all items where successfully added; otherwise false.</returns>
		public static bool AddBatchTrx<TADD>(IEnumerable<TADD> items)
			where TADD : IDataEntityBatchAddTrx, TDataEntity
		{
			return AddBatchTrx<TADD>(items, IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// Adds a collection of new <typeparamref name="TADD"/> items to the database using a SqlTransaction
		/// using the desired <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityBatchAddTrx"/> object</typeparam>
		/// <param name="items">The collection items containing the new information.</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <returns>true if all items where successfully added; otherwise false.</returns>
		public static bool AddBatchTrx<TADD>(IEnumerable<TADD> items, IsolationLevel isolationLevel)
			where TADD : IDataEntityBatchAddTrx, TDataEntity
		{
			bool succcess = false;
			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{

					succcess = AddBatchTrx<TADD>(items, command);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (succcess)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion
				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}
			return succcess;
		}
		/// <summary>
		/// Adds a collection of new <typeparamref name="TADD"/> items to the database using the
		/// provided SqlCommand.
		/// </summary>
		/// <typeparam name="TADD">An <see cref="IDataEntityBatchAddTrx"/> object</typeparam>
		/// <param name="items">The collection items containing the new information.</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <returns>true if all items where successfully added; otherwise false.</returns>
		public static bool AddBatchTrx<TADD>(IEnumerable<TADD> items, SqlCommand command)
			where TADD : IDataEntityBatchAddTrx, TDataEntity
		{
			Guard.ArgumentNotNull(command, "command");
			Guard.ArgumentNotNull(items, "items");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			if (items.Count() == 0)
				return true;
			bool succcess = false;
			foreach (var item in items)
			{
				command.Parameters.Clear();
				succcess = item.AddNewBatchItem(command);
				if (!succcess)
					break;
			}
			return succcess;
		}

		#endregion

		#region Update

		/// <summary>
		/// Updates an existing <typeparamref name="TUPDATE"/> item in the database
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityUpdate"/></typeparam>
		/// <param name="item">An instance of type <typeparamref name="TUPDATE"/> containing the object to update</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int UpdateItem<TUPDATE>(TUPDATE item)
			where TUPDATE : IDataEntityUpdate, TDataEntity
		{
			return UpdateItem<TUPDATE>(item, null);
		}
		/// <summary>
		/// Updates an existing <typeparamref name="TUPDATE"/> item in the database
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityUpdate"/></typeparam>
		/// <param name="item">An instance of type <typeparamref name="TUPDATE"/> containing the object to update</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int UpdateItem<TUPDATE>(TUPDATE item, object[] state)
			where TUPDATE : IDataEntityUpdate, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			int retVal = -99;

			using (var da = new DataAccess(ResolveConnection()))
				retVal = item.Update(da, state);

			return retVal;
		}
		/// <summary>
		/// Updates an existing <typeparamref name="TUPDATE"/> item in the database using a SqlTransaction
		/// with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityUpdate"/></typeparam>
		/// <param name="item">An instance of type <typeparamref name="TUPDATE"/> containing the object to update</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int UpdateItemTrx<TUPDATE>(TUPDATE item, object[] state)
			where TUPDATE : IDataEntityUpdateTrx, TDataEntity
		{
			return UpdateItemTrx<TUPDATE>(item, state, IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// Updates an existing <typeparamref name="TUPDATE"/> item in the database using a SqlTransaction
		/// with the desired <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityUpdate"/></typeparam>
		/// <param name="item">An instance of type <typeparamref name="TUPDATE"/> containing the object to update</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int UpdateItemTrx<TUPDATE>(TUPDATE item, object[] state, IsolationLevel isolationLevel)
			where TUPDATE : IDataEntityUpdateTrx, TDataEntity
		{
			int result = -99;

			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{

					result = UpdateItemTrx(item, command, state);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (result == 0)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion

				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}

			return result;
		}
		/// <summary>
		/// Updates an existing <typeparamref name="TUPDATE"/> item in the database using the
		/// provided SqlCommand, returning an Int32 containing the return value of the 
		/// database call. Assumes you're managing the SqlTransaction.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityUpdate"/></typeparam>
		/// <param name="item">An instance of type <typeparamref name="TUPDATE"/> containing the object to update</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <param name="state">Optional state containing any additional data.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int UpdateItemTrx<TUPDATE>(TUPDATE item, SqlCommand command, object[] state)
			where TUPDATE : IDataEntityUpdateTrx, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			Guard.ArgumentNotNull(command, "command");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			command.Parameters.Clear();
			return item.Update(command, state);
		}

		/// <summary>
		/// Updates a collection of <typeparamref name="TUPDATE"/> items in the database.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityBatchUpdate"/></typeparam>
		/// <param name="items">A collection of type <typeparamref name="TUPDATE"/> containing the objects to update</param>
		/// <returns>true if all items where successfully updated; otherwise false.</returns>
		public static bool UpdateBatch<TUPDATE>(IEnumerable<TUPDATE> items)
			where TUPDATE : IDataEntityBatchUpdate, TDataEntity
		{
			Guard.ArgumentNotNull(items, "items");
			if (items.Count() == 0)
				return true;
			using (var da = new DataAccess(ResolveConnection()))
			{
				foreach (var item in items)
				{
					if (!item.UpdateBatchItem(da))
						return false;
				}
			}

			return true;
		}
		/// <summary>
		/// Updates a collection of <typeparamref name="TUPDATE"/> items in the database using a SqlTransaction
		/// with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityBatchUpdateTrx"/></typeparam>
		/// <param name="items">A collection of type <typeparamref name="TUPDATE"/> containing the objects to update</param>
		/// <returns>true if all items where successfully updated; otherwise false.</returns>
		public static bool UpdateBatchTrx<TUPDATE>(IEnumerable<TUPDATE> items)
			where TUPDATE : IDataEntityBatchUpdateTrx, TDataEntity
		{
			return UpdateBatchTrx<TUPDATE>(items, IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// Updates a collection of <typeparamref name="TUPDATE"/> items in the database using a SqlTransaction
		/// with the desired <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityBatchUpdateTrx"/></typeparam>
		/// <param name="items">A collection of type <typeparamref name="TUPDATE"/> containing the objects to update</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <returns>true if all items where successfully updated; otherwise false.</returns>
		public static bool UpdateBatchTrx<TUPDATE>(IEnumerable<TUPDATE> items, IsolationLevel isolationLevel)
			where TUPDATE : IDataEntityBatchUpdateTrx, TDataEntity
		{
			bool success = false;

			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{

					success = UpdateBatchTrx(items, command);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (success)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion
				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}

			return success;
		}
		/// <summary>
		/// Updates a collection of <typeparamref name="TUPDATE"/> items in the database using the
		/// provided SqlCommand.
		/// </summary>
		/// <typeparam name="TUPDATE">A Type oftype <see cref="IDataEntityBatchUpdateTrx"/></typeparam>
		/// <param name="items">A collection of type <typeparamref name="TUPDATE"/> containing the objects to update</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <returns>true if all items where successfully updated; otherwise false.</returns>
		public static bool UpdateBatchTrx<TUPDATE>(IEnumerable<TUPDATE> items, SqlCommand command)
			where TUPDATE : IDataEntityBatchUpdateTrx, TDataEntity
		{
			Guard.ArgumentNotNull(command, "command");
			Guard.ArgumentNotNull(items, "items");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			if (items.Count() == 0)
				return true;

			bool success = false;

			foreach (var item in items)
			{
				command.Parameters.Clear();
				success = item.UpdateBatchItem(command);
				if (!success)
					break;
			}

			return success;
		}

		#endregion

		#region Delete

		/// <summary>
		/// Deletes a <typeparamref name="TDELETE"/> item from the database
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityDelete"/></typeparam>
		/// <param name="item">An instance of type IDataEntityDelete containing the object to delete</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int DeleteItem<TDELETE>(TDELETE item)
			where TDELETE : IDataEntityDelete, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			int retVal = -99;

			using (var da = new DataAccess(ResolveConnection()))
				retVal = item.Delete(da);

			return retVal;
		}
		/// <summary>
		/// Deletes a <typeparamref name="TDELETE"/> item from the database using a SqlTransaction
		/// with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityDelete"/></typeparam>
		/// <param name="item">An instance of type IDataEntityDelete containing the object to delete</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int DeleteItemTrx<TDELETE>(TDELETE item)
			where TDELETE : IDataEntityDeleteTrx, TDataEntity
		{
			return DeleteItemTrx<TDELETE>(item, IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// Deletes a <typeparamref name="TDELETE"/> item from the database using a SqlTransaction
		/// with the desired <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityDelete"/></typeparam>
		/// <param name="item">An instance of type IDataEntityDelete containing the object to delete</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int DeleteItemTrx<TDELETE>(TDELETE item, IsolationLevel isolationLevel)
			where TDELETE : IDataEntityDeleteTrx, TDataEntity
		{
			int result = -99;

			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{
					result = DeleteItemTrx<TDELETE>(item, command);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (result == 0)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion
				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}

			return result;
		}
		/// <summary>
		/// Deletes a <typeparamref name="TDELETE"/> item from the database using the
		/// provided SqlCommand, returning an Int32 containing the return value of the 
		/// database call. Assumes you're managing the SqlTransaction.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityDelete"/></typeparam>
		/// <param name="item">An instance of type IDataEntityDelete containing the object to delete</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <returns>An int containing the return value of the database call.</returns>
		public static int DeleteItemTrx<TDELETE>(TDELETE item, SqlCommand command)
			where TDELETE : IDataEntityDeleteTrx, TDataEntity
		{
			Guard.ArgumentNotNull(item, "item");
			Guard.ArgumentNotNull(command, "command");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			command.Parameters.Clear();
			return item.Delete(command);
		}

		/// <summary>
		/// Deletes a collection of type <typeparamref name="TDELETE"/> items from the database.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityBatchDelete"/>.</typeparam>
		/// <param name="items">A collection of type TDELETE containing the objects to delete</param>
		/// <returns>true if the items where successfully deleted; otherwise false.</returns>
		public static bool DeleteBatch<TDELETE>(IEnumerable<TDELETE> items)
			where TDELETE : IDataEntityBatchDelete, TDataEntity
		{
			Guard.ArgumentNotNull(items, "items");
			if (items.Count() == 0)
				return true;

			bool success = false;

			using (var da = new DataAccess(ResolveConnection()))
			{
				foreach (var item in items)
				{
					success = item.DeleteBatchItem(da);
					if (!success)
						break;
				}
			}

			return success;
		}
		/// <summary>
		/// Deletes a collection of type <typeparamref name="TDELETE"/> items from the database using a SqlTransaction
		/// with a ReadCommitted <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityBatchDelete"/>.</typeparam>
		/// <param name="items">A collection of type TDELETE containing the objects to delete</param>
		/// <returns>true if all items where successfully deleted; otherwise false.</returns>
		public static bool DeleteBatchTrx<TDELETE>(IEnumerable<TDELETE> items)
			where TDELETE : IDataEntityBatchDeleteTrx, TDataEntity
		{
			return DeleteBatchTrx<TDELETE>(items, IsolationLevel.ReadCommitted);
		}
		/// <summary>
		/// Deletes a collection of type <typeparamref name="TDELETE"/> items from the database using a SqlTransaction
		/// with the desired <see cref="IsolationLevel"/>.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityBatchDelete"/>.</typeparam>
		/// <param name="items">A collection of type TDELETE containing the objects to delete</param>
		/// <param name="isolationLevel">The desired <see cref="IsolationLevel"/> of the transaction.</param>
		/// <returns>true if all items where successfully deleted; otherwise false.</returns>
		public static bool DeleteBatchTrx<TDELETE>(IEnumerable<TDELETE> items, IsolationLevel isolationLevel)
			where TDELETE : IDataEntityBatchDeleteTrx, TDataEntity
		{
			bool success = false;

			using (SqlConnection connection = new SqlConnection(ResolveConnectionString()))
			{
				#region Begin Transaction
				connection.Open();
				SqlCommand command = connection.CreateCommand();
				SqlTransaction transaction = connection.BeginTransaction(isolationLevel); // Start a local transaction.
				command.Connection = connection;
				command.Transaction = transaction;
				command.CommandType = CommandType.StoredProcedure;
				#endregion

				try
				{

					success = DeleteBatchTrx<TDELETE>(items, command);

					#region Complete Transaction
					// The downstream item may have closed the connection or committed/rolled back the transaction.
					if (connection.State == ConnectionState.Open && (null != transaction.Connection))
					{
						if (success)
							transaction.Commit();
						else
							transaction.Rollback();
					}
					#endregion

				}
				catch (Exception ex)
				{
					try
					{
						if (connection.State == ConnectionState.Open && (null != transaction.Connection))
							transaction.Rollback();
					}
					catch { }
					throw ex;
				}
			}

			return success;
		}
		/// <summary>
		/// Deletes a collection of type <typeparamref name="TDELETE"/> items from the database using the
		/// provided SqlCommand.
		/// </summary>
		/// <typeparam name="TDELETE">A Type oftype <see cref="IDataEntityBatchDelete"/>.</typeparam>
		/// <param name="items">A collection of type TDELETE containing the objects to delete</param>
		/// <param name="command">An existing <see cref="SqlCommand"/> instance that is connected to an open connection.</param>
		/// <returns>true if all items where successfully deleted; otherwise false.</returns>
		public static bool DeleteBatchTrx<TDELETE>(IEnumerable<TDELETE> items, SqlCommand command)
			where TDELETE : IDataEntityBatchDeleteTrx, TDataEntity
		{
			Guard.ArgumentNotNull(command, "command");
			Guard.ArgumentNotNull(items, "items");
			if (null == command.Transaction)
				throw new Exception(Strings.DataAccessService_MissingTrx);
			if (items.Count() == 0)
				return true;

			bool success = false;

			foreach (var item in items)
			{
				command.Parameters.Clear();
				success = item.DeleteBatchItem(command);
				if (!success)
					break;
			}

			return success;
		}
		#endregion

		#endregion
	}
	#endregion
}